
<!DOCTYPE html>
<!--
  Copyright 2011 Google Inc. All Rights Reserved.

  Licensed under the Apache License, Version 2.0 (the "License");
  you may not use this file except in compliance with the License.
  You may obtain a copy of the License at

      http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing, software
  distributed under the License is distributed on an "AS IS" BASIS,
  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  See the License for the specific language governing permissions and
  limitations under the License.
-->
<html>
  <head>
    <title>BDU Tools</title>

    <style type="text/css">
      body { font-family: Arial; }
    </style>
	
   	<style type="text/css">
		.mytable {
		border:1px solid #000000;
		border-collapse:collapse;
		width:200px;
		}
		.mytable td{
		border:1px solid #000000;
		}
		.mytable th{
		background:#cccccc;
		border:1px solid #000000;
		}
	</style>
	<script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
	google.load('visualization', '1', {packages:['table']});
	//google.load("visualization", "1", {packages:["corechart"]});
	// Load the Visualization API and the controls package.
    google.load('visualization', '1.0', {'packages':['controls']});
	var scopes = 'https://www.googleapis.com/auth/fusiontables';
	var clientId = '863565692657.apps.googleusercontent.com';
	var apiKey = 'AIzaSyCj4FjUE6NDN0wvxZ0npSfAaBjbQaYhEEo';
	var tableId='1SWRyFw9bylieqNBhCLAiUz6vx4mqrx6N1mdjD6E';
	var market=[['United States of America','Canada','Mexico'],[ 'United Kingdom','France','Sweden','Italy','Germany','Spain'],['Japan'],['Australia'],['India','China','Vietnam']];
	var resp1=null;
	var dataTable;
	var table;
	var isAuth=false;
	var selectedItems=[];
	var respRowId;
	
	//var categoryFilter='Company';
      // Initialize the client, set onclick listeners.
      function initialize() {
        gapi.client.setApiKey(apiKey);
        document.getElementById('select-data').onclick = viewTable;
		if(isAuth==false)
			document.getElementById('toggle-button').value="Login";
		else
			document.getElementById('toggle-button').value="Logout";
		document.getElementById('toggle-button').onclick = function() { auth(false); return false;};
        window.setTimeout(function() { auth(true); }, 1);
      }
	
	function logOut(){
			document.location.href="https://www.google.com/accounts/Logout?continue=https://appengine.google.com/_ah/logout?continue=http://bdutools2611.appspot.com";
		}
      // Run OAuth 2.0 authorization.
      function auth(immediate) {
        gapi.auth.authorize({
          client_id: clientId,
          scope: scopes,
          immediate: immediate
        }, handleAuthResult);
      }

      // Handle the results of the OAuth 2.0 flow.
      function handleAuthResult(authResult) {
        var authorizeButton = document.getElementById('toggle-button');
        if (authResult && !authResult.error) {
		 	authorizeButton.value="Logout";
			authorizeButton.onclick=logOut;
			isAuth=true;
			//checkUser();
        } else {
		  authorizeButton.value="Login";
          authorizeButton.onclick = function() { auth(true); return false; };
        }
      }
		
		
	/*function checkUser(){
		query('SELECT * FROM'+tableId);
	}*/
	
	
	/*function viewPieChart(){
		var date1=document.getElementById('date1').value;
		var date2=document.getElementById('date2').value;
		var query = "SELECT Location, COUNT() as Count "+
					"FROM "+tableId+" "+ 
					"WHERE Meeting_date>=" + "'" + date1 + "'" + " AND Meeting_date<=" + "'" + date2 + "' "+ 
					"GROUP BY Location ORDER BY COUNT() DESC";
		
        drawPieChartQuery(query); //resp1 chua json
	}
	// Send an SQL query to Fusion Tables.
      function drawPieChartQuery(query) {
        var lowerCaseQuery = query.toLowerCase();
        var path = '/fusiontables/v1/query';
        var callback = function(element) {
          return function(resp) {
		  	resp1=resp;
		  	drawPieChart();
          };
        }
		if (lowerCaseQuery.indexOf('select') != 0 &&
            lowerCaseQuery.indexOf('show') != 0 &&
            lowerCaseQuery.indexOf('describe') != 0) {

          var body = 'sql=' + encodeURIComponent(query);
          runClientRequest({
            path: path,
            body: body,
            headers: {
              'Content-Type': 'application/x-www-form-urlencoded',
              'Content-Length': body.length
            },
            method: 'POST'
          }, callback('insert-data-output'));

        }
          runClientRequest({
            path: path,
            params: { 'sql': query }
          }, callback('select-data-output'));
      }
	  function drawPieChart() {
		var tempArray=[];
		for(var k=0; k<=resp1.rows.length; k++)
		{
				tempArray[k]=[];
		}
		tempArray[0]=[resp1.columns[0],resp1.columns[1]];
		for(var i=0; i<resp1.rows.length; i++)
			for(var j=0; j<resp1.rows[i].length; j++)
			{
				if(j%2!=0){
					resp1.rows[i][j]=parseInt(resp1.rows[i][j])
				}
				tempArray[i+1][j]=resp1.rows[i][j];
			};
		  
		  var tempMarket=[['Market','Count'],['North America',0],['Europe',0],['Japan',0],['Australia',0],['Asia',0],['Other',0]];
		  for(var hang=1 ; hang<=resp1.rows.length; hang++)
		  {
			var temp=0;
			for (var area= 0 ; area<6 ; area++)
			{
				if (area==5)
					{
						tempMarket[6][1] = tempMarket[6][1] + tempArray[hang][1];
						break;
					}

				for (var country=0; country<(market[area].length); country++)

				{
					if ( tempArray[hang][0] == market[area][country] )

					{
						tempMarket[area+1][1] = tempMarket[area+1][1]+tempArray[hang][1];
						temp = 1;
						break;
					}
				}
				if (temp==1) 
				{
					temp = 0;
					break;
				}
			}
		  }
		  array=tempMarket;
		  flag=true;
		  // Set a callback to run when the Google Visualization API is loaded.
		  google.setOnLoadCallback(drawChart());	
      }
	function drawChart() {
		var data = google.visualization.arrayToDataTable(array);
		var options = {
			title: 'My Customer'
		};

		var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
		chart.draw(data, options); 
	}*/
		
	
	function viewTable(){
		query('SELECT * FROM '+tableId);
	}
	
	function drawTable(){
		if(resp1.rows!=null){
			var tempArray=[];
			for(var k=0; k<=resp1.rows.length; k++)
			{
				tempArray[k]=[];
			}
			//tempArray[0][0]='No.';
			for(var k=0; k<resp1.columns.length; k++)
			{
				tempArray[0][k]=resp1.columns[k];
			}
			for(var i=0; i<resp1.rows.length; i++){
				//tempArray[i+1][0]=i+1;
				for(var j=0; j<resp1.rows[i].length; j++){
					tempArray[i+1][j]=resp1.rows[i][j];
				}
			}
			dataTable=tempArray;
		}
		//draw chart wrapper
		var data = google.visualization.arrayToDataTable(dataTable);
		table = new google.visualization.ChartWrapper({
			'chartType': 'Table',
			'containerId': 'table_div',
			'options': {'page':'enable','pageSize':'15' ,'pagingSymbols':{'prev': 'prev', 'next': 'next'}, 'showRowNumber':'true', 'sortAscending':'true'}
		  });
		google.visualization.events.addListener(table, 'select', function() {
		 	var selection = table.getChart().getSelection();
			
			var selected = "";
			for (var i = 0; i < selection.length; i++) {
			  var item = selection[i];
			  if (item.row != null && item.column != null) {
				var str = data.getFormattedValue(item.row, item.column);
				selected +=str+"/";
			  } else if (item.row != null) {
				var str = data.getFormattedValue(item.row, 0);
				selected += str+"/";
			  } else if (item.column != null) {
				var str = data.getFormattedValue(0, item.column);
				selected += str+"/";
			  }
			}
			selectedItems=selected.split("/");
		});
		//Define a StringFilter control for the 'Name' column
		var slctCategoryFilter=document.getElementById('slctCategoryFilter');
		slctCategoryFilter.style.display="block";
		var stringFilter = new google.visualization.ControlWrapper({
			'controlType': 'StringFilter',
			'containerId': 'control1',
			'options': {
			'filterColumnLabel': slctCategoryFilter.value
			 }
		  });
		   // Create the dashboard.
 		 var dashboard = new google.visualization.Dashboard(document.getElementById('dashboard'));
		 dashboard.bind(stringFilter, table);
		 dashboard.draw(data);
	}
	
	function deleteRows(){
		if(selectedItems.length>0)
		{
			var r=confirm("Do you want to delete "+(selectedItems.length-1)+" row(s)?");
			if (r==true)
			{
				for(var i=0; i<selectedItems.length-1; i++){
					getMyRowIds(selectedItems[i]);
				}
			}
		}
		else
			alert("Please choose record(s) you want to delete");
	}
	
	function getMyRowIds(timeStamp){
		queryRowId("SELECT ROWID FROM "+tableId+" WHERE Timestamp='"+timeStamp+"'");
	}
	
	function deleteRow(rowId){
		queryDeleteRowById("DELETE FROM "+tableId+" WHERE ROWID='"+rowId+"'");
	}
	
	function queryDeleteRowById(query){
		var lowerCaseQuery = query.toLowerCase();
		var path = '/fusiontables/v1/query';
		var callback = function(element) {
		  return function(resp) {
		  		selectedItems=null;
		 		viewTable();
		  	};
		 }
		 if (lowerCaseQuery.indexOf('select') != 0 &&
            lowerCaseQuery.indexOf('show') != 0 &&
            lowerCaseQuery.indexOf('describe') != 0) {

          var body = 'sql=' + encodeURIComponent(query);
          runClientRequest({
            path: path,
            body: body,
            headers: {
              'Content-Type': 'application/x-www-form-urlencoded',
              'Content-Length': body.length
            },
            method: 'POST'
          }, callback('insert-data-output'));

        }
	  runClientRequest({
		path: path,
		params: { 'sql': query }
	  }, callback('select-data-output'));
	}
	
	function queryRowId(query){
		var lowerCaseQuery = query.toLowerCase();
		var path = '/fusiontables/v1/query';
		var callback = function(element) {
		  return function(resp) {
			if(respRowId=resp.rows[0][0])
				respRowId=resp.rows[0][0];
			deleteRow(respRowId);
		  };
		 }
		 if (lowerCaseQuery.indexOf('select') != 0 &&
            lowerCaseQuery.indexOf('show') != 0 &&
            lowerCaseQuery.indexOf('describe') != 0) {

          var body = 'sql=' + encodeURIComponent(query);
          runClientRequest({
            path: path,
            body: body,
            headers: {
              'Content-Type': 'application/x-www-form-urlencoded',
              'Content-Length': body.length
            },
            method: 'POST'
          }, callback('insert-data-output'));

        } 
	  runClientRequest({
		path: path,
		params: { 'sql': query }
	  }, callback('select-data-output'));
	}
	// The selection handler.
	// Loop through all items in the selection and concatenate
	// a single message from all of them.
	
	/*function createTable(){
		var nrCols=resp1.columns.length;
		var nrRows=resp1.rows.length;
		var root=document.getElementById('mydiv');
		var tab=document.createElement('table');
		tab.className="mytable";
		var tbo=document.createElement('tbody');
		var thd=document.createElement('thead');
		var row, cell, header;
		thd.appendChild(document.createTextNode("Id"));
		for(var k=0; k<nrCols; k++){
			header=document.createElement('th');
			header.appendChild(document.createTextNode(resp1.columns[k]));
			thd.appendChild(header);
		}
		thd.appendChild(document.createTextNode(""));
		tab.appendChild(thd);
		
		for(var i=0; i<nrRows; i++){
			row=document.createElement('tr');
			var rowId=document.createElement('td');
			rowId.appendChild(document.createTextNode(i+1));
			row.appendChild(rowId);
			for(var j=0; j<nrCols; j++){
				cell=document.createElement('td');
				cell.appendChild(document.createTextNode(resp1.rows[i][j]));
				row.appendChild(cell);
			}
			var checkbox = document.createElement('input');
			checkbox.type = "checkbox";
			checkbox.name = "chk_"+i;
			checkbox.value = "";
			checkbox.id = "chk_"+i;
			row.appendChild(checkbox);
			row.style.border = "1px solid #000";
			tbo.appendChild(row);
		}
		tab.appendChild(tbo);
		root.appendChild(tab);
	}*/		
      // Send an SQL query to Fusion Tables.
      function query(query) {
        var lowerCaseQuery = query.toLowerCase();
        var path = '/fusiontables/v1/query';
        var callback = function(element) {
          return function(resp) {
			resp1=resp;
			drawTable();
			};
        }
        if (lowerCaseQuery.indexOf('select') != 0 &&
            lowerCaseQuery.indexOf('show') != 0 &&
            lowerCaseQuery.indexOf('describe') != 0) {

          var body = 'sql=' + encodeURIComponent(query);
          runClientRequest({
            path: path,
            body: body,
            headers: {
              'Content-Type': 'application/x-www-form-urlencoded',
              'Content-Length': body.length
            },
            method: 'POST'
          }, callback('insert-data-output'));

        }
          runClientRequest({
            path: path,
            params: { 'sql': query }
          }, callback('select-data-output'));
      }

      // Execute the client request.
      function runClientRequest(request, callback) {
        var restRequest = gapi.client.request(request);
        restRequest.execute(callback);
      }
    </script>
     <script src="https://apis.google.com/js/client.js?onload=initialize"></script>
  </head>
  <body>
    <div><input type="button" id="toggle-button" value="unknown"></div>
	<p id="select-data-output"></p>
	<input type="button" onClick="deleteRows();" value="Delete"/>
	<div id='dashboard'>
		<select id="slctCategoryFilter" onChange="viewTable()" style="display:none">
			<option value="Location"  selected="selected">Location</option>
			<option value="Company Name">Company Name</option>
			<option value="Channel">Channel</option>
			<option value="Domain">Domain</option>
		</select>
		<div id='control1'></div>
		<div id='table_div'></div>
	</div>
	<!--input type="date"id="date1"><input type="date"id="date2"--><input type='button' id='select-data' value='Select data'><br>
	<div id='chart_div' style='width: 900px; height: 500px;'></div>
  </body>
</html>
